package com.gljx.web.controller;


import com.gljx.constant.ActivitiKey;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import com.gljx.web.service.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/")
public class ChangeClazzController {

	@Autowired
	private ChangeClazzService changeClazzService;
	
	@Autowired
	private DepartmentService departmentService;
	
	@Autowired
	private ClazzService clazzService;
	
	@Autowired
	private StudentService studentService;
	
	@Autowired
	private StudentController studentController;
	
	@Autowired
	private CustomLogService customLogService;

	Axamine axamine = new Axamine();
	/**
	 * 查询转班申请记录
	 * @param request
	 * @return
	 * @createTime 2017-11-14 2:06 pm
	 * @author zyq.
	 * @throws Exception
	 */
	@RequestMapping(value="/selChangeClazz/look.action")
	public String selChangeClazz(HttpServletRequest request) throws Exception{
		HttpSession session=request.getSession();
		User user=(User) session.getAttribute("user");                                   //获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
		         ca.setTime(new Date()); // 设置时间为当前时间
		         ca.add(Calendar.DATE, -1);// 月份减1
		Date resultDate = ca.getTime(); // 结果
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String beginTime = sdf.format(resultDate);
		String endTime = sdf.format(new Date());
		PageBean<ChangeClazz> pBean=getPageBean(request, department.getFid(), beginTime, endTime);
//		rhc  信息总条数
		String messageAmount="共有"+pBean.getTr()+"条信息";
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("changeClazzs", pBean.getBeanList());
		request.setAttribute("pb", pBean);
		request.setAttribute("company", department);
//			rhc  信息总条数
		request.setAttribute("messageAmount",messageAmount);
		return "backHome/changeClazz/changeClazzList";
	}
	
	/**
	 * 新增转班申请
	 * @createTime 2017-11-14 3:24 pm
	 * @author zyq.
	 * @param request
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/addChangeClazz")
	public String addChangeClazz(HttpServletRequest request) throws Exception{
		HttpSession session=request.getSession();
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
		         ca.setTime(new Date()); // 设置时间为当前时间
		         ca.add(Calendar.DATE, -1);// 月份减1
	    Date resultDate = ca.getTime(); // 结果
	    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	    String beginTime = sdf.format(resultDate);
	    String endTime = sdf.format(new Date());
		User user=(User) session.getAttribute("user");                                  		//获取已经登录用户数据
		Department department=departmentService.queryAdminByFid(user.getFcompanyid());  	   //获取登录用户组织ID
        Map map = studentController.getPageAsTrainCharge(request, "", "", "0", "","" ,"",beginTime, endTime); //调用学员方法 //调用学员方法
		List<Clazz> clazzs=clazzService.selectByCompanyID(user.getFcompanyid());
		request.setAttribute("department", department);
		request.setAttribute("clazzs", clazzs);
		request.setAttribute("flag", 1);				//是否为新增记录	
		request.setAttribute("beginTime", beginTime);
        request.setAttribute("endTime", endTime);
		request.setAttribute("pb", map.get("pb"));
        request.setAttribute("students", map.get("sList"));
		return "backHome/changeClazz/detailChangeClazz";	
	}
	
	/**
	 * 根据ID查询学员
	 * @param request
	 * @param fid
	 * @createTime 2017-11-15 3:24 pm
	 * @author zyq.
	 * @return
	 */
	@RequestMapping(value="/selStudentById")
	@ResponseBody
	public Student selStudentById(HttpServletRequest request,String fid){
		Student student=studentService.selectStudentById(fid);
		request.getSession().setAttribute("fid", student.getFid());
		return student;
	}
 	
	
	/**
	 * 保存转班申请记录
	 * @param request
	 * @param changeClazz
	 * @createTime 2017-11-16 9:24 am
	 * @author zyq.
	 * @return
	 */
	@RequestMapping(value="/saveChangeClazz")
	public String saveChangeClazz(HttpServletRequest request,ChangeClazz changeClazz){
				//获取操作人信息
				User user = (User) request.getSession().getAttribute("user");
				//利用UUID生成fid
				changeClazz.setFid(GetFid.UUIDIntoFid());
				//控制单元id
				changeClazz.setFcontrolunitid(user.getFcompanyid());
				//创建人id
				changeClazz.setFcreatorid(user.getFid());
				//公司id
				changeClazz.setCfficompanyid(user.getFcompanyid());
				//获取当前时间
				String createTime = TimeFormat.timeFormat(new Date());
				changeClazz.setFcreatetime(createTime);
				/*String fnumber = customLogService.selectFunmber("CT_DS_TRANSFERCLAZZREG",user.getFcompanyid());*/
				changeClazz.setFnumber(GetFnumber.fnumbers("ZBSQ"));
				changeClazz.setFhandlerid(user.getFid());
				changeClazz.setCfbillstate((long) 3);
				changeClazzService.insertChangeClazz(changeClazz);
				/*student.setCfclazzid(changeClazz.getCfzrbjid());
				student.setCfamount(changeClazz.getCfbjje());
				student.setFid(changeClazz.getCfstudentid());
				studentService.updateStudentSelective(student);*/
				/*customLogService.addLog("学员转班申请", "办理了转班申请", changeClazz.getFid(), user.getFid());*/
				customLogService.addLog("学员转班申请", "办理了学员"+changeClazz.getCfstudentno()+""+changeClazz.getStudentName()+"的转班申请,转入班级："+changeClazz.getCfzrbjName()+",转入班级金额:"+changeClazz.getCfbjje()+",单据编号:"+changeClazz.getFnumber() , changeClazz.getCfstudentid(), user.getFid());
				request.getSession().setAttribute("changeClazzId", changeClazz.getFid());
				request.setAttribute("type",2);
				return "forward:addChangeClazz";
	}
	
	
	/**
	 * 根据FID查询转班申请
	 * @param request
	 * @param fid
	 * @createTime 2017-11-16 11:24 am
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/selChangeClazzById")
	public String selChangeClazzById(HttpServletRequest request,String fid) throws Exception{
		fid = fid.replace(" ", "+");
		HttpSession session = request.getSession();
		User user = (User) session.getAttribute("user");
		ChangeClazz changeClazz=changeClazzService.selectChangeClazzById(fid);
		if(changeClazz == null)
		return "/error/not_data";
		else {
			if (changeClazz.getCfbillstate() == 3) {
				Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
				ca.setTime(new Date()); // 设置时间为当前时间
				ca.add(Calendar.DATE, -1);// 月份减1
				Date resultDate = ca.getTime(); // 结果
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				String beginTime = sdf.format(resultDate);
				String endTime = sdf.format(new Date());
				Map map = studentController.getPageAsTrainCharge(request,"","", "0", "", "", "",beginTime, endTime); //调用学员方法 //调用学员方法
				List<Clazz> clazzs = clazzService.selectByCompanyID(user.getFcompanyid());
				request.setAttribute("clazzs", clazzs);
				request.setAttribute("beginTime", beginTime);
				request.setAttribute("endTime", endTime);
				request.setAttribute("pb", map.get("pb"));
				request.setAttribute("students", map.get("sList"));
				request.setAttribute("changeClazz", changeClazz);
				request.setAttribute("flag", 2);
			} else {
				request.setAttribute("changeClazz", changeClazz);
				request.setAttribute("flag", 2);
			}
			boolean seeFlag = axamine.selectAuditPersonByAdd(changeClazz.getFnumber(), ActivitiKey.PUBLIC_PROCESS);
			request.setAttribute("seeFlag", seeFlag);
			return "backHome/changeClazz/detailChangeClazz";
		}
	}

	/**
	 * 提交
	 * @param fnumber
	 * @return
	 */
	@RequestMapping(value = "/submitChangeClazz")
	@ResponseBody
	public ServerResponse submitChangeClazz(String fnumber, String fid,HttpServletRequest request){
		User user=(User) request.getSession().getAttribute("user");
		ChangeClazz changeClazz = new ChangeClazz();
					changeClazz.setFid(fid);
					changeClazz.setFnumber(fnumber);
					changeClazz.setCfbillstate((long) 4);
		ChangeClazz changeClazz2 = changeClazzService.selectChangeClazzById(fid);
		changeClazzService.submitFlow(changeClazz);
		changeClazzService.updateChangeClazz(changeClazz);
		if(changeClazz2 != null)
		customLogService.addLog("学员转班申请",  "提交了学员"+changeClazz2.getCfstudentno()+""+changeClazz2.getStudentName()+"的转班申请,单据编码:"+changeClazz2.getFnumber(), changeClazz2.getCfstudentid(), user.getFid());
		return ServerResponse.createBySuccessMessage("提交成功");
	}
	
	/**
	 * 更新转班申请
	 * @param request
	 * @param changeClazz
	 * @createTime 2017-11-16 11:24 am
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/updateChangeClazz")
	public String updateChangeClazz(HttpServletRequest request,ChangeClazz changeClazz) throws Exception{
		User user=(User) request.getSession().getAttribute("user");
		String updateTime=TimeFormat.timeFormat(new Date());
				changeClazz.setFlastupdatetime(updateTime);
				changeClazz.setFlastupdateuserid(user.getFid());
		changeClazzService.updateChangeClazz(changeClazz);
		selChangeClazz(request);
		request.setAttribute("type",2);
		customLogService.addLog("学员转班申请",  "修改了转班申请", changeClazz.getCfstudentid(), user.getFid());
		return "backHome/changeClazz/changeClazzList";
	}

	@RequestMapping(value = "/outChangeClazzExcel")
	public void outChangeClazzExcel(HttpServletResponse response, HttpServletRequest request,
								  ChangeClazz changeClazz, String beginTime, String endTime) throws Exception {
		//获取操作人信息
		User user = (User) request.getSession().getAttribute("user");
		List<Object> data = new ArrayList<>();
		//查询数据
		PageBean<ChangeClazz> pBean =  getPageBean1(request,user.getFcompanyid(),beginTime,endTime,changeClazz,true);
		for (ChangeClazz changeClazz1 : pBean.getBeanList()) {
			data.add(changeClazz1.getFnumber());//单据编号
			data.add(changeClazz1.getFbizdate());//业务日期
			data.add(changeClazz1.getFdescription());
			data.add(changeClazz1.getFauditorName());
			data.add(changeClazz1.getFcreateName());//制单人名称
			data.add(changeClazz1.getStudentName());//学员
			data.add(changeClazz1.getCfstudentno());//学员编码
			data.add(changeClazz1.getCfidcardno());//身份证号
			data.add(changeClazz1.getCfmobile());
			if (changeClazz1.getCfbillstate() == 3) {
				data.add("保存");//单据状态
			} else if (changeClazz1.getCfbillstate() == 4) {
				data.add("提交");//单据状态
			} else if (changeClazz1.getCfbillstate() == 5) {
				data.add("审核");//单据状态
			} else {
				data.add("");//单据状态
			}
			data.add(changeClazz1.getCfphone());
			if (changeClazz1.getCfsex() == null) {
				data.add("");//性别
			} else if (changeClazz1.getCfsex() == 2) {
				data.add("女");//性别
			} else {
				data.add("男");//性别
			}
			data.add(changeClazz1.getCfrxrq());
			data.add(changeClazz1.getCfcurrentclassName());
			data.add(changeClazz1.getCfamount());
			data.add(changeClazz1.getCfzrbjName());
			data.add(changeClazz1.getCfbjje());
		}
		//头部数据
		String head[] = {"单据编码", "业务日期", "备注","审核人名称", "制单人名称","学员","学员编码", "身份证号码","手机","单据状态","电话", "性别",
				"入学日期", "当前班级", "当前班级金额", "转入班级", "转入班级金额"};
		//长度
		Integer length[] = {7500, 3000, 6000, 3000, 3000, 3000, 6200, 6200, 3000, 2000, 3000, 2600, 4000, 4000, 3000, 4000, 3000};
		try {
			//导出Excel
			CommonExport.importExcel(response, "转班申请" + TimeFormat.getNowTime2() + ".xls", "转班申请", data, head, length);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	/**
	 * 根据FID批量删除转班申请
	 * @param request
	 * @param fid
	 * @createTime 2017-11-16 11:24 am
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/delChangeClazzById")
	public String delChangeClazzById(HttpServletRequest request,String fid) throws Exception{
		User user=(User) request.getSession().getAttribute("user");
		String[] fids = fid.split(",");
		for(int i=0 ;i<fids.length;i++){
			Student student = new Student();
			ChangeClazz changeClazz = changeClazzService.selectChangeClazzById(fids[i]);
			List<Student> list = studentService.selectStudentByFid(changeClazz.getCfstudentid());
			if(!list.isEmpty())
				 	student = list.get(0);
			customLogService.addLog("学员转班申请", "删除了学员:"+student.getFnumber()+student.getFnameL2()+"的转班申请,单据编码:"+changeClazz.getFnumber(), student.getFid(), user.getFid());
		}
		changeClazzService.delChangeClazzById(fids);
		selChangeClazz(request);
		return "backHome/changeClazz/changeClazzList";
	}
	
	
	/**
	 * 进行审核
	 * @param request
	 * @param fid
	 * @createTime 2017-11-18 11:24 am
	 * @author zyq.
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/checkChangeBillState")
	@ResponseBody
	public ServerResponse checkChangeBillState(HttpServletRequest request,String fid,String state,String agreed,String auditcomment,String fnumber) throws Exception {
		/*User user = (User) request.getSession().getAttribute("user");
		ChangeClazz changeClazz = new ChangeClazz();
					changeClazz.setFid(fid);
					changeClazz.setCfbillstate((long) 5);					//更改转班申请状态
					changeClazz.setFauditorid(user.getFid());
		changeClazzService.updateChangeClazz(changeClazz);		
		ChangeClazz changeClazz2 = changeClazzService.selectChangeClazzById(fid);
		Student student = new Student();                                    //审核通过后，修改学员信息
				student.setCfclazzid(changeClazz2.getCfzrbjid());
				student.setCfamount(changeClazz2.getCfbjje());
				student.setFid(changeClazz2.getCfstudentid());
		studentService.updateStudentSelective(student);
		selChangeClazz(request);
		customLogService.addLog("学员转班申请", changeClazz2.getStudentName() + "办理了转班申请", changeClazz2.getCfstudentid(), user.getFid());
		return "backHome/changeClazz/changeClazzList";*/
		User user=(User) request.getSession().getAttribute("user");
		ChangeClazz changeClazz = new ChangeClazz();
		ChangeClazz changeClazz2 = changeClazzService.selectChangeClazzById(fid);
					changeClazz.setFauditorid(user.getFid());
					changeClazz.setCfbillstate((long) 5);
					changeClazz.setFid(fid);
					changeClazz.setFnumber(fnumber);
					changeClazz.setStudentName(changeClazz2.getStudentName());
					changeClazz.setCfstudentid(changeClazz2.getCfstudentid());
					changeClazz.setCfzrbjid(changeClazz2.getCfzrbjid());
					changeClazz.setCfbjje(changeClazz2.getCfbjje());
					changeClazz.setCfamount(changeClazz2.getCfamount());
		if ("3".equalsIgnoreCase(state))
			customLogService.addLog("学员转班申请", "反审核了学员:" +changeClazz2.getCfstudentno()+""+ changeClazz2.getStudentName() + "转班申请,单据编号:"+changeClazz2.getFnumber(), changeClazz2.getCfstudentid(), user.getFid());
		else
			customLogService.addLog("学员转班申请", "审核了学员:"+changeClazz2.getCfstudentno()+""+changeClazz2.getStudentName() + "转班申请,单据编号:"+changeClazz2.getFnumber(), changeClazz2.getCfstudentid(), user.getFid());
		return ServerResponse.createBySuccessMessage(changeClazzService.checkChangeClazz(changeClazz,state,agreed,auditcomment));
	}
	
	
	/**
	 * 根据用户输入条件查询转班申请记录
	 * @param request
	 * @param changeClazz 条件
	 * @param beginTime 开始时间	
	 * @param endTime	结束时间
	 * @createTime 2017-11-20 10:24 am
	 * @author zyq.
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping(value="/selectChangeClazzByCon")
	public String selectChangeClazzByCon(HttpServletRequest request,ChangeClazz changeClazz,String beginTime, String endTime,String outList[]) throws Exception {
		HttpSession session = request.getSession();
		User user = (User) session.getAttribute("user");                                   //获取已经登录用户数据
		Department department = departmentService.queryAdminByFid(user.getFcompanyid());   //获取登录用户组织ID
		PageBean<ChangeClazz> pBean=getPageBean1(request, department.getFid(), beginTime, endTime,changeClazz,false);
		//		rhc  信息总条数
		String messageAmount="共有"+pBean.getTr()+"条信息";
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("changeClazzs", pBean.getBeanList());
		request.setAttribute("pb", pBean);
		request.setAttribute("changeClazz1", changeClazz);
		request.setAttribute("company", department);
		//		rhc  信息总条数
		request.setAttribute("messageAmount",messageAmount);
		request.getSession().setAttribute("outList",outList);
        System.out.println(outList);
        return "backHome/changeClazz/changeClazzList";
	}

    /**
     * 转班申请筛选字段显示
     * @param p 获取要显示的数组
     * @param request
     * @return
     */
    @RequestMapping(value="/OutList")
    @ResponseBody
    public Object OutList(@RequestBody(required=true) Map<String,Object> p,HttpServletRequest request)  {
	    ArrayList<String> arr= (ArrayList<String>) p.get("outList");

	    request.getSession().setAttribute("outlist",arr);
        return arr;
    }




    @SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<ChangeClazz> getPageBean(HttpServletRequest request,String fid,String beginTime,String endTime) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		String psql="select to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
				   +"to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
				   + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
				   + "to_char(T.CFRXRQ,'YYYY-MM-DD')  cfrxrq,"
				   + "T.FID, T.FCREATORID, T.FCONTROLUNITID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
				   + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID, T.CFSTUDENTID, T.CFSTUDENTNO, T.CFIDCARDNO, "
				   + "T.CFMOBILE, T.CFPHONE, T.CFSEX, T.CFAMOUNT, T.CFSHJE, T.CFZRBJID, T.CFBJJE, T.CFBILLSTATE,T.CFCURRENTCLASSID,"
				   + "U.FNAME_L2 AS STUDENTNAME,S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME,C.FNAME_L2 AS CFCURRENTCLASSNAME,C1.FNAME_L2 AS CFZRBJNAME "
			   		+ "  FROM CT_DS_TRANSFERCLAZZREG T LEFT JOIN  CT_DS_STUDENT U ON T.CFSTUDENTID=U.FID"
			   		+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
			   		+ " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID LEFT JOIN CT_DS_CLAZZ C ON T.CFCURRENTCLASSID=C.FID LEFT JOIN CT_DS_CLAZZ C1 ON T.CFZRBJID=C1.FID"
			   		+ " WHERE t.CFFICOMPANYID= '"+fid+"' and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '"+beginTime+" ' AND '"+endTime+"'";
		psql +="  	order by to_char(t.FBIZDATE,'yyyy-MM-dd') desc";
		int psize=15; //每页显示数
		int pindex=offset/psize;//+1  页码从0开始	
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= changeClazzService.selectPage(pindex, psql, psize);
		PageBean<ChangeClazz> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<ChangeClazz>)paramMap.get("v_cur")); //每页数据集
		return pb;

	}

	
	@SuppressWarnings("unchecked")
	/**
	 * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
	 * @return 返回PageBean实体类 
	 * @throws Exception
	 */
	public PageBean<ChangeClazz> getPageBean1(HttpServletRequest request,String fid,String beginTime,String endTime,ChangeClazz changeClazz,boolean isExcle) throws Exception
	{

		int offset;//从第几条数据开始加载
		if(request.getParameter("pager.offset")==null){  
			offset=0;  
		}else{  
			offset=Integer.parseInt(request.getParameter("pager.offset"));  
		}	
		String psql="select to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
				   +"to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
				   + "to_char(t.FBIZDATE,'yyyy-MM-DD')  fbizdate,"
				   + "to_char(T.CFRXRQ,'YYYY-MM-DD')  cfrxrq,"
				   + "GP.CFGUIFEI, GP.CFPXF,U.CFSLZJCXDH,"
				   + "T.FID, T.FCREATORID, T.FCONTROLUNITID, T.FNUMBER, T.FHANDLERID, T.FDESCRIPTION, T.FHASEFFECTED, T.FAUDITORID, T.FSOURCEBILLID,"
				   + "T.FSOURCEFUNCTION, T.FFIVOUCHERED, T.CFFICOMPANYID, T.CFSTUDENTID, T.CFSTUDENTNO, T.CFIDCARDNO, "
				   + "T.CFMOBILE, T.CFPHONE, T.CFSEX, T.CFAMOUNT, T.CFSHJE, T.CFZRBJID, T.CFBJJE, T.CFBILLSTATE,T.CFCURRENTCLASSID,"
				   + "U.FNAME_L2 AS STUDENTNAME,S.NAME AS FCREATENAME,S1.NAME AS FLASTUPDATENAME,S2.NAME AS FHANDLERNAME,S3.NAME AS FAUDITORNAME,C.FNAME_L2 AS CFCURRENTCLASSNAME,C1.FNAME_L2 AS CFZRBJNAME "
					+ "  FROM CT_DS_TRANSFERCLAZZREG T LEFT JOIN  CT_DS_STUDENT U ON T.CFSTUDENTID=U.FID"
			   		+ " LEFT JOIN T_E_USER S ON T.FCREATORID=S.FID LEFT JOIN T_E_USER S1 ON T.FLASTUPDATEUSERID=S1.FID "
			   		+ " LEFT JOIN T_E_USER S2 ON T.FHANDLERID=S2.FID LEFT JOIN T_E_USER S3 ON T.FAUDITORID=S3.FID LEFT JOIN CT_DS_CLAZZ C "
					+ "ON T.CFCURRENTCLASSID=C.FID LEFT JOIN CT_DS_CLAZZ C1 ON T.CFZRBJID=C1.FID LEFT JOIN CT_DS_TRAINCHARGEENTRY GP ON  GP.CFSTUDENTID=U.FID"
			   		+ " WHERE t.CFFICOMPANYID= '"+fid+"'  ";
					if (changeClazz.getCfmobile() == null || changeClazz.getCfmobile() == "") {
			
					} else {
						psql += "and t.cfmobile like '%" + changeClazz.getCfmobile() + "%'";
					}
					if (changeClazz.getCfidcardno() == null || changeClazz.getCfidcardno() == "") {
			
					} else {
						psql += "and t.cfidcardno like '%" + changeClazz.getCfidcardno() + "%'";
					}
					if (changeClazz.getStudentName() == null || changeClazz.getStudentName() == "") {
			
					} else {
						psql += "and u.fname_l2 like '%" + changeClazz.getStudentName() + "%'";
					}
					if (beginTime.equals(null) || beginTime.equals("")|| endTime.equals(null) || endTime.equals("")) {
					}else {
						psql += "and to_char(t.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'";
					}
						psql +="order by to_char(t.FBIZDATE,'yyyy-MM-dd') desc";
		Map<String, Object> paramMapCount=getPageBeanCount(psql); //取记录总数
		int psize = 0;
		if(isExcle)
			if((int)paramMapCount.get("Pcount") == 0)
				psize = 1;
			else
				psize = (int)paramMapCount.get("Pcount");
			else
		   		psize=100; //每页显示数
		int pindex=offset/psize;//+1  页码从0开始
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap= changeClazzService.selectPage(pindex, psql, psize);
		PageBean<ChangeClazz> pb=new PageBean<>();
		pb.setPc(pindex); //页码
		pb.setTr((int)paramMapCount.get("Pcount")); //记录总数
		pb.setPs(psize);
		pb.setBeanList((List<ChangeClazz>)paramMap.get("v_cur")); //每页数据集
		return pb;

	}

	
	
	
	
	
	/**
	 * 取记录总数 dmz 20160921
	 * @param psql  SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception
	{

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap=changeClazzService.selectPageCount(psql);
		return paramMap;		
	}
}
